package com.mcp.converter.domain.parser.impl;

import com.fasterxml.jackson.databind.ObjectMapper;
import com.fasterxml.jackson.databind.node.ArrayNode;
import com.fasterxml.jackson.databind.node.ObjectNode;
import com.mcp.converter.domain.parser.IfpXMLParser;
import com.mcp.converter.domain.rule.manager.RuleManager;
import com.mcp.converter.shared.response.analysis.BatchAnalysisResponse;
import com.mcp.converter.shared.response.analysis.CompatibilityReclassificationResult;
import com.mcp.converter.shared.response.analysis.FallbackCompatibilityResult;
import com.mcp.converter.shared.response.xml.XMLParseResult;
import com.mcp.converter.shared.response.sql.SQLLocation;
import com.mcp.converter.shared.enums.SQLType;
import lombok.extern.slf4j.Slf4j;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.JSQLParserException;
import org.springframework.stereotype.Component;

import java.io.IOException;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;
import java.util.stream.Stream;

/**
 * IFP框架XML文件解析器实现类
 * 专门用于解析IFP框架XML文件并提取其中的SQL语句
 * @author zengxiong
 */
@Slf4j
@Component
public class IfpXMLParserImpl implements IfpXMLParser {
    
    private static final List<String> SQL_PROPERTY_NAMES = Arrays.asList(
            "execSql", "querySql", "updateSql", "insertSql", "deleteSql"
    );
    
    // 用于匹配所有包含SQL的bl:property标签的通用模式
    private static final Pattern ALL_BL_PROPERTY_PATTERN = Pattern.compile(
        "<bl:property\\s+name\\s*=\\s*[\"']([^\"']+)[\"']\\s+value\\s*=\\s*\"([^\"]*)\"[^>]*/?>",
        Pattern.CASE_INSENSITIVE | Pattern.DOTALL
    );
    
    private final ObjectMapper objectMapper;
    private final RuleManager ruleManager;
    
    public IfpXMLParserImpl(RuleManager ruleManager) {
        this.objectMapper = new ObjectMapper();
        this.ruleManager = ruleManager;
    }

    @Override
    public XMLParseResult parseFileContent(String xmlContent, String virtualPath) {
        XMLParseResult result = XMLParseResult.forIfp(virtualPath);
        result.setXmlPath(virtualPath);
        result.setParseSuccess(true);
        
        try {
            // 提取业务逻辑ID
            String businessLogicId = extractBusinessLogicId(xmlContent);
            result.setNamespace(businessLogicId);
            
            // 提取SQL语句
            List<SQLLocation> sqlLocations = extractSQLStatementsFromContent(xmlContent, virtualPath);
            result.setSqlLocations(sqlLocations);
            
            log.info("IFP框架XML解析成功: {}, 业务逻辑ID: {}, SQL数量: {}", 
                    virtualPath, businessLogicId, sqlLocations.size());
            
        } catch (Exception e) {
            log.error("解析IFP框架XML内容失败: {}", virtualPath, e);
            result.setParseSuccess(false);
            result.setErrorMessage("解析失败: " + e.getMessage());
        }
        
        return result;
    }

    @Override
    public String extractSQLsFromXML(String xmlFilePath) {
        long startTime = System.currentTimeMillis();
        
        try {
            // 验证文件路径
            if (xmlFilePath == null || xmlFilePath.trim().isEmpty()) {
                return formatErrorResponse("必须提供有效的XML文件路径");
            }
            
            // 读取XML文件内容
            String xmlContent = readXmlFileContent(xmlFilePath);
            if (xmlContent == null) {
                return formatErrorResponse("无法读取XML文件：" + xmlFilePath);
            }
            
            // 提取文件名用于日志标识
            String fileName = extractFileNameFromPath(xmlFilePath);
            log.info("开始从IFP框架XML文件提取SQL语句: {}", fileName);
            
            // 解析XML内容
            XMLParseResult parseResult = parseFileContent(xmlContent, fileName);
            
            // 对每条SQL进行自动修复，确保为标准SQL
            if (parseResult.getSqlLocations() != null && !parseResult.getSqlLocations().isEmpty()) {
                log.info("=== 开始处理提取的SQL语句 ===");
                log.info("文件: {}, 总SQL数量: {}", fileName, parseResult.getSqlLocations().size());
                
                for (SQLLocation sqlLocation : parseResult.getSqlLocations()) {
                    String originalSql = sqlLocation.getSqlContent();
                    String fixedSql = fixIfpSqlToStandardSql(originalSql, sqlLocation.getElementType());
                    
                    // 记录原始SQL
                    sqlLocation.setOriginalSql(originalSql);
                    // 设置修复后的SQL
                    sqlLocation.setSqlContent(fixedSql);
                    
                    // 详细的日志输出
                    log.info("=== SQL #{} 处理详情 ===", sqlLocation.getSqlId());
                    log.info("类型: {}", sqlLocation.getElementType());
                    log.info("SQL类型: {}", sqlLocation.getSqlType());
                    log.info("原始SQL (完整):\n{}", originalSql);
                    log.info("修复后SQL (完整):\n{}", fixedSql);
                    log.info("原始SQL长度: {}, 修复后SQL长度: {}", originalSql.length(), fixedSql.length());
                    log.info("=== SQL #{} 处理完成 ===\n", sqlLocation.getSqlId());
                }
                
                log.info("=== 所有SQL处理完成 ===");
            }
            
            String result = buildSQLExtractionResponse(parseResult, fileName);
            long duration = System.currentTimeMillis() - startTime;
            log.info("IFP框架SQL提取并修复完成: {}, SQL数量: {}, 耗时: {}ms",
                    fileName,
                    parseResult.getSqlLocations() != null ? parseResult.getSqlLocations().size() : 0,
                    duration);
            return result;
            
        } catch (Exception e) {
            long errorTime = System.currentTimeMillis() - startTime;
            log.error("IFP框架SQL提取失败: {}, 耗时: {}ms, 错误: {}", xmlFilePath, errorTime, e.getMessage(), e);
            return formatErrorResponse("SQL提取失败: " + e.getMessage());
        }
    }

    @Override
    public String fixIfpSqlToStandardSql(String sql, String actionType) {
        if (sql == null || sql.trim().isEmpty()) {
            return sql;
        }
        
        // 检查是否为非IFP XML文件（如Spring配置文件）
        if (isNonIfpXmlFile(sql)) {
            log.info("检测到非IFP XML文件，跳过处理");
            return "-- 非IFP XML文件，已跳过SQL解析";
        }
        
        String fixedSql = sql.trim();
        
        // 1. 处理转义字符
        fixedSql = fixedSql.replace("&lt;", "<")
                          .replace("&gt;", ">")
                          .replace("&amp;", "&")
                          .replace("&quot;", "\"")
                          .replace("&apos;", "'");
        
        // 2. 移除多余的空白字符
        fixedSql = fixedSql.replaceAll("\\s+", " ").trim();
        
        // 3. 特殊处理INSTR函数，更通用地处理第二个参数
        fixedSql = fixedSql.replaceAll("(?i)INSTR\\(([^,]+),\\s*([a-zA-Z0-9_]+)\\)", "INSTR($1, '$2')");
        
        // 4. 处理IFP参数绑定语法：将 #param 替换为 ?
        fixedSql = fixedSql.replaceAll("#[a-zA-Z0-9_]+", "?");
        
        // 5. 处理字符串拼接操作符 '||'，将其替换为CONCAT函数以提高兼容性
        // 处理简单的 a||b 形式
        fixedSql = fixedSql.replaceAll("(?i)([a-zA-Z0-9_?'\"]+)\\s*\\|\\|\\s*([a-zA-Z0-9_?'\"]+)", 
                                      "CONCAT($1, $2)");
        
        // 处理复杂的 a||b||c 形式
        while (fixedSql.matches("(?i).*[a-zA-Z0-9_?'\"]+\\s*\\|\\|\\s*[a-zA-Z0-9_?'\"]+\\s*\\|\\|.*")) {
            fixedSql = fixedSql.replaceFirst("(?i)([a-zA-Z0-9_?'\"]+)\\s*\\|\\|\\s*([a-zA-Z0-9_?'\"]+)\\s*\\|\\|\\s*([a-zA-Z0-9_?'\"]+)", 
                                           "CONCAT(CONCAT($1, $2), $3)");
        }
        
        // 5. 使用JSQLParser进行语法验证和修复
        fixedSql = validateAndFixIfpSqlSyntax(fixedSql);
        
        // 6. 确保SQL语句以分号结尾（如果原本没有）
        if (!fixedSql.endsWith(";") && !fixedSql.endsWith(",")) {
            fixedSql += ";";
        }
        
        return fixedSql;
    }

    /**
     * 使用JSQLParser验证和修复IFP框架SQL语法
     */
    private String validateAndFixIfpSqlSyntax(String sql) {
        if (sql == null || sql.trim().isEmpty()) {
            return sql;
        }
        
        try {
            // 准备用于验证的SQL（替换参数占位符）
            String sqlForValidation = prepareIfpSqlForValidation(sql);
            
            // 尝试使用JSQLParser解析
            CCJSqlParserUtil.parse(sqlForValidation);
            log.debug("IFP框架SQL语法验证通过: {}", sql);
            return sql;
            
        } catch (JSQLParserException e) {
            log.warn("IFP框架SQL语法验证失败，尝试修复: {} - 错误: {}", sql, e.getMessage());
            
            // 尝试基本语法修复
            String fixedSql = attemptIfpBasicSyntaxFixes(sql);
            
            try {
                // 再次验证修复后的SQL
                String fixedSqlForValidation = prepareIfpSqlForValidation(fixedSql);
                CCJSqlParserUtil.parse(fixedSqlForValidation);
                log.info("IFP框架SQL语法修复成功: {} -> {}", sql, fixedSql);
                return fixedSql;
                
            } catch (JSQLParserException e2) {
                log.warn("IFP框架SQL语法修复失败，返回原始SQL: {} - 错误: {}", fixedSql, e2.getMessage());
                return sql; // 返回原始SQL，保持非破坏性
            }
        }
    }
    
    /**
     * 为JSQLParser验证准备IFP框架SQL
     */
    private String prepareIfpSqlForValidation(String sql) {
        if (sql == null || sql.trim().isEmpty()) {
            return sql;
        }
        
        String prepared = sql;
        
        // 替换IFP框架参数占位符 #param 为测试值
        prepared = prepared.replaceAll("#([a-zA-Z0-9_]+)", "'test_value'");
        
        // 替换空字符串参数
        prepared = prepared.replace("''", "'test_value'");
        
        return prepared;
    }
    
    /**
     * 尝试IFP框架基本语法修复
     */
    private String attemptIfpBasicSyntaxFixes(String sql) {
        if (sql == null || sql.trim().isEmpty()) {
            return sql;
        }
        
        String fixed = sql;
        
        // 1. 修复多余的逗号
        fixed = fixed.replaceAll(",\\s*(?=FROM|WHERE|GROUP|ORDER|HAVING|LIMIT|;|$)", "");
        
        // 2. 修复FROM子句中的AND条件（IFP框架常见问题）
        // 将 "FROM table alias AND condition" 修复为 "FROM table alias WHERE condition"
        Pattern fromAndPattern = Pattern.compile(
            "(?i)FROM\\s+([a-zA-Z0-9_]+)\\s+([a-zA-Z0-9_]+)\\s+AND\\s+(.+?)(?=;|$)",
            Pattern.CASE_INSENSITIVE | Pattern.DOTALL
        );
        Matcher fromAndMatcher = fromAndPattern.matcher(fixed);
        if (fromAndMatcher.find()) {
            String tableName = fromAndMatcher.group(1);
            String alias = fromAndMatcher.group(2);
            String condition = fromAndMatcher.group(3).trim();
            fixed = fromAndMatcher.replaceFirst("FROM " + tableName + " " + alias + " WHERE " + condition);
        }
        
        // 3. 修复不完整的WHERE子句
        fixed = fixed.replaceAll("(?i)WHERE\\s*$", "");
        fixed = fixed.replaceAll("(?i)WHERE\\s+AND\\s+", "WHERE ");
        fixed = fixed.replaceAll("(?i)WHERE\\s+OR\\s+", "WHERE ");
        
        // 4. 确保SQL以分号结尾
        if (!fixed.trim().endsWith(";")) {
            fixed = fixed.trim() + ";";
        }
        
        return fixed;
    }

    @Override
    public boolean isSQLComplete(String sqlContent) {
        if (sqlContent == null || sqlContent.trim().isEmpty()) {
            return false;
        }
        
        String trimmedSql = sqlContent.trim().toLowerCase();
        
        // 检查是否是完整的SQL语句
        return trimmedSql.startsWith("select") ||
               trimmedSql.startsWith("insert") ||
               trimmedSql.startsWith("update") ||
               trimmedSql.startsWith("delete") ||
               trimmedSql.startsWith("create") ||
               trimmedSql.startsWith("drop") ||
               trimmedSql.startsWith("alter") ||
               trimmedSql.startsWith("truncate");
    }

    @Override
    public String extractFileNameFromPath(String filePath) {
        if (filePath == null || filePath.trim().isEmpty()) {
            return "unknown";
        }
        
        Path path = Paths.get(filePath);
        return path.getFileName().toString();
    }

    @Override
    public String readXmlFileContent(String xmlFilePath) {
        try {
            Path path = Paths.get(xmlFilePath);
            if (!Files.exists(path)) {
                log.error("XML文件不存在: {}", xmlFilePath);
                return null;
            }
            
            return Files.readString(path);
        } catch (IOException e) {
            log.error("读取XML文件失败: {}", xmlFilePath, e);
            return null;
        }
    }
    
    /**
     * 获取SQL中包含的所有不兼容函数
     */
    private Set<String> getIncompatibleFunctionsInSql(String sql) {
        Set<String> foundFunctions = new HashSet<>();
        
        if (sql == null || sql.trim().isEmpty()) {
            return foundFunctions;
        }

        // 使用规则管理器检查不兼容函数
        Set<String> incompatibleFunctions = ruleManager.getAllIncompatibleFunctions();
        log.info("从RuleManager获取的不兼容函数列表: {}", incompatibleFunctions);
        String upperSql = sql.toUpperCase();

        for (String function : incompatibleFunctions) {
            // 修改：使用更精确的匹配方式，避免将#field#误识别为USER函数
            if (function != null && !function.isEmpty()) {
                // 创建正则表达式来确保函数名是独立出现的
                String functionRegex = "\\b" + Pattern.quote(function.toUpperCase()) + "\\b";
                Pattern pattern = Pattern.compile(functionRegex);
                Matcher matcher = pattern.matcher(upperSql);
                
                if (matcher.find()) {
                    log.warn("SQL语句 '{}' 中发现不兼容函数: {}", sql, function);
                    foundFunctions.add(function);
                }
            }
        }

        return foundFunctions;
    }

    @Override
    public List<String> scanXmlFiles(String directoryPath, boolean recursive) {
        List<String> xmlFiles = new ArrayList<>();
        
        try {
            Path startPath = Paths.get(directoryPath);
            if (!Files.exists(startPath) || !Files.isDirectory(startPath)) {
                log.warn("目录不存在或不是有效目录: {}", directoryPath);
                return xmlFiles;
            }
            
            try (Stream<Path> paths = recursive ? 
                    Files.walk(startPath) : 
                    Files.list(startPath)) {
                
                xmlFiles = paths
                    .filter(Files::isRegularFile)
                    .filter(path -> path.toString().toLowerCase().endsWith(".xml"))
                    .map(Path::toString)
                    .collect(Collectors.toList());
            }
            
            log.info("扫描目录 {} 找到 {} 个XML文件，递归: {}", directoryPath, xmlFiles.size(), recursive);
            
        } catch (IOException e) {
            log.error("扫描目录失败: {}", directoryPath, e);
        }
        
        return xmlFiles;
    }

    @Override
    public FallbackCompatibilityResult performFallbackCompatibilityCheck(List<String> validSqls, BatchAnalysisResponse batchResult) {
        FallbackCompatibilityResult result = new FallbackCompatibilityResult();

        if (validSqls == null || validSqls.isEmpty()) {
            return result;
        }

        log.info("开始执行回退兼容性检查，SQL数量: {}", validSqls.size());

        // 检查每个SQL中是否包含不兼容的函数
        for (int i = 0; i < validSqls.size(); i++) {
            String sql = validSqls.get(i);
            log.info("检查SQL #{}: {}", i, sql);
            
            // 获取该SQL中包含的所有不兼容函数
            Set<String> foundIncompatibleFunctions = getIncompatibleFunctionsInSql(sql);
            
            if (!foundIncompatibleFunctions.isEmpty()) {
                result.getIncompatibleSqlIndexes().add(i);
                log.warn("SQL #{} 被标记为不兼容，包含函数: {}", i, foundIncompatibleFunctions);

                // 记录第一个发现的不兼容函数（避免重复计数）
                String firstFunction = foundIncompatibleFunctions.iterator().next();
                result.getFunctionDetails().put(String.valueOf(i), "包含不兼容函数: " + firstFunction);
                log.warn("不兼容函数: {}", firstFunction);
            } else {
                log.info("SQL #{} 兼容", i);
            }
        }

        log.info("回退兼容性检查完成，发现 {} 个不兼容的SQL", result.getIncompatibleSqlIndexes().size());
        return result;
    }

    /**
     * 执行智能重分类 - 严格按照 SQL提取 → JSqlParser语法校验 → RuleManager兼容性校验 流程
     */
    @Override
    public CompatibilityReclassificationResult performIntelligentReclassification(BatchAnalysisResponse batchResult, List<String> validSqls) {
        if (batchResult == null || validSqls == null) {
            return new CompatibilityReclassificationResult(0, 0, 0);
        }
        
        int originalErrorCount = batchResult.getErrorCount();
        int correctedIncompatibleCount = batchResult.getIncompatibleCount();
        int correctedErrorCount = originalErrorCount;
        
        log.debug("开始智能错误重分类 - 原始统计: 兼容={}, 不兼容={}, 错误={}", 
                 batchResult.getCompatibleCount(), correctedIncompatibleCount, originalErrorCount);
        
        // 针对每个有效SQL进行标准化的三步分析流程
        if (!validSqls.isEmpty() && batchResult.getErrors() != null && !batchResult.getErrors().isEmpty()) {
            for (int i = 0; i < validSqls.size(); i++) {
                String sql = validSqls.get(i);
                
                // 检查此SQL是否在错误列表中
                boolean isMarkedAsError = batchResult.getErrors().containsKey(i) || batchResult.getErrors().containsKey(i + 1);
                
                if (isMarkedAsError) {
                    // 执行标准化的三步分析流程
                    AnalysisResult analysisResult = performStandardizedAnalysis(sql, i);
                    
                    switch (analysisResult.getStatus()) {
                        case COMPATIBLE:
                            correctedErrorCount--;
                            log.debug("SQL[{}] 重新分类：错误 → 兼容 (通过标准化分析)", i);
                            break;
                        case INCOMPATIBLE:
                            correctedIncompatibleCount++;
                            correctedErrorCount--;
                            log.debug("SQL[{}] 重新分类：错误 → 不兼容 (通过标准化分析)", i);
                            break;
                        case ERROR:
                            // 保持错误状态
                            log.debug("SQL[{}] 保持错误状态 (标准化分析失败)", i);
                            break;
                    }
                }
            }
        }
        
        // 确保错误计数不为负数
        if (correctedErrorCount < 0) {
            correctedErrorCount = 0;
        }
        
        log.debug("智能重分类完成 - 修正后统计: 兼容={}, 不兼容={}, 错误={}", 
                 batchResult.getCompatibleCount(), correctedIncompatibleCount, correctedErrorCount);
        
        return new CompatibilityReclassificationResult(
            originalErrorCount, correctedIncompatibleCount, correctedErrorCount);
    }
    
    /**
     * 执行标准化的三步分析流程：SQL提取 → JSqlParser语法校验 → RuleManager兼容性校验
     */
    private AnalysisResult performStandardizedAnalysis(String sql, int index) {
        log.debug("开始对SQL[{}]执行标准化三步分析流程", index);
        
        try {
            // 第一步：SQL提取和清理（已经完成，这里是提取后的SQL）
            String cleanedSql = sql;
            if (cleanedSql == null || cleanedSql.trim().isEmpty()) {
                return new AnalysisResult(AnalysisStatus.ERROR, "SQL为空");
            }
            
            // 第二步：JSqlParser语法校验
            boolean syntaxValid = performJSqlParserValidation(cleanedSql);
            if (!syntaxValid) {
                log.debug("SQL[{}] JSqlParser语法校验失败，检查是否为有效的SQL结构", index);
                
                // 如果JSqlParser校验失败，但SQL具有有效结构，继续进行兼容性校验
                if (!isValidSqlStructure(cleanedSql)) {
                    return new AnalysisResult(AnalysisStatus.ERROR, "SQL语法无效且结构不完整");
                }
                
                log.debug("SQL[{}] 虽然JSqlParser校验失败，但具有有效SQL结构，继续兼容性校验", index);
            }
            
            // 第三步：RuleManager兼容性校验
            boolean hasIncompatibleElements = performRuleManagerCompatibilityCheck(cleanedSql);
            if (hasIncompatibleElements) {
                return new AnalysisResult(AnalysisStatus.INCOMPATIBLE, "包含不兼容的函数或语法");
            }
            
            // 如果通过所有检查，则认为是兼容的
            return new AnalysisResult(AnalysisStatus.COMPATIBLE, "通过所有兼容性检查");
            
        } catch (Exception e) {
            log.error("标准化分析过程中发生异常: {}", e.getMessage(), e);
            return new AnalysisResult(AnalysisStatus.ERROR, "分析过程异常: " + e.getMessage());
        }
    }
    
    /**
     * 执行JSqlParser语法校验
     */
    private boolean performJSqlParserValidation(String sql) {
        try {
            // 准备用于验证的SQL（替换参数占位符）
            String sqlForValidation = prepareIfpSqlForValidation(sql);
            
            // 尝试使用JSQLParser解析
            CCJSqlParserUtil.parse(sqlForValidation);
            log.debug("JSqlParser语法校验通过");
            return true;
            
        } catch (JSQLParserException e) {
            log.debug("JSqlParser语法校验异常: {}", e.getMessage());
            return false;
        } catch (Exception e) {
            log.debug("JSqlParser语法校验异常: {}", e.getMessage());
            return false;
        }
    }
    
    /**
     * 执行RuleManager兼容性校验
     */
    private boolean performRuleManagerCompatibilityCheck(String sql) {
        try {
            // 使用RuleManager检查不兼容函数
            Set<String> foundIncompatibleFunctions = getIncompatibleFunctionsInSql(sql);
            if (!foundIncompatibleFunctions.isEmpty()) {
                String foundFunction = foundIncompatibleFunctions.iterator().next();
                log.debug("RuleManager兼容性校验：发现不兼容函数 {}", foundFunction);
                return true;
            }
            
            // 可以在这里添加更多的RuleManager规则检查
            // 例如：不兼容的数据类型、语法结构等
            
            log.debug("RuleManager兼容性校验：未发现不兼容元素");
            return false;
            
        } catch (Exception e) {
            log.error("RuleManager兼容性校验异常: {}", e.getMessage(), e);
            // 异常情况下，保守地认为可能存在不兼容问题
            return true;
        }
    }
    
    /**
     * 检查SQL是否具有有效的结构
     */
    private boolean isValidSqlStructure(String sql) {
        if (sql == null || sql.trim().isEmpty()) {
            return false;
        }
        
        String upperSql = sql.trim().toUpperCase();
        
        // 检查是否包含基本的SQL关键字结构
        return (upperSql.startsWith("SELECT") && upperSql.contains("FROM")) ||
               (upperSql.startsWith("INSERT") && upperSql.contains("INTO")) ||
               (upperSql.startsWith("UPDATE") && upperSql.contains("SET")) ||
               (upperSql.startsWith("DELETE") && upperSql.contains("FROM")) ||
               upperSql.startsWith("CREATE") ||
               upperSql.startsWith("ALTER") ||
               upperSql.startsWith("DROP");
    }
    
    /**
     * 分析结果枚举
     */
    private enum AnalysisStatus {
        COMPATIBLE,    // 兼容
        INCOMPATIBLE,  // 不兼容
        ERROR          // 错误
    }
    
    /**
     * 分析结果类
     */
    private static class AnalysisResult {
        private final AnalysisStatus status;
        private final String message;
        
        public AnalysisResult(AnalysisStatus status, String message) {
            this.status = status;
            this.message = message;
        }
        
        public AnalysisStatus getStatus() {
            return status;
        }
        
        public String getMessage() {
            return message;
        }
    }
     
     /**
     * 从XML内容中提取业务逻辑ID
     */
    private String extractBusinessLogicId(String xmlContent) {
        // 提取bl:businessLogic的id属性
        Pattern pattern = Pattern.compile(
            "<bl:businessLogic\\s+[^>]*id\\s*=\\s*[\"']([^\"']+)[\"'][^>]*>",
            Pattern.CASE_INSENSITIVE | Pattern.DOTALL
        );
        Matcher matcher = pattern.matcher(xmlContent);
        
        if (matcher.find()) {
            return matcher.group(1);
        }
        
        return "unknown";
    }
    
    /**
     * 从XML内容中提取SQL语句
     */
    private List<SQLLocation> extractSQLStatementsFromContent(String xmlContent, String xmlPath) {
        List<SQLLocation> sqlLocations = new ArrayList<>();
        
        // 1. 使用原有方式提取已知的SQL属性
        for (String propertyName : SQL_PROPERTY_NAMES) {
            extractSQLsByPropertyName(xmlContent, xmlPath, propertyName, sqlLocations);
        }
        
        // 2. 使用通用模式提取所有bl:property标签中的SQL
        extractAllBlPropertySQLs(xmlContent, xmlPath, sqlLocations);
        
        log.info("从IFP框架XML提取到 {} 个SQL语句: {}", sqlLocations.size(), xmlPath);
        return sqlLocations;
    }
    
    /**
     * 根据属性名提取SQL语句
     * 修改：增加对IFP框架参数绑定语法的兼容性处理
     */
    private void extractSQLsByPropertyName(String xmlContent, String xmlPath, 
                                          String propertyName, List<SQLLocation> sqlLocations) {
        // 匹配 <bl:property name="propertyName" value="sql语句" />
        Pattern pattern = Pattern.compile(
            "<bl:property\\s+name\\s*=\\s*[\"']" + Pattern.quote(propertyName) + "[\"']\\s+value\\s*=\\s*\"([^\"]*)\"",
            Pattern.CASE_INSENSITIVE | Pattern.DOTALL
        );
        Matcher matcher = pattern.matcher(xmlContent);
        
        // 新增：定义参数绑定模式
        Pattern paramPattern = Pattern.compile("#([a-zA-Z0-9_]+)");
        
        int sqlIndex = 1;
        while (matcher.find()) {
            String sqlContent = matcher.group(1);
            // 修改：即使SQL内容为空也要记录，但标记为空SQL
            if (sqlContent != null) {
                SQLLocation location = new SQLLocation();
                location.setXmlPath(xmlPath);
                location.setSqlId(propertyName + "_" + sqlIndex);
                
                // 处理空SQL的情况
                if (sqlContent.trim().isEmpty()) {
                    location.setSqlContent("");
                    log.debug("发现空{}语句: {}, 位置: 行{}", propertyName, location.getSqlId(), calculateLineNumber(xmlContent, matcher.start()));
                } else {
                    // 新增：处理IFP框架的参数绑定语法
                    String processedSql = sqlContent.trim();
                    // 将 #param 替换为 ?
                    processedSql = paramPattern.matcher(processedSql).replaceAll("?");
                    
                    location.setSqlContent(processedSql);
                    log.debug("提取到{}语句: {}, 内容: {}", propertyName, location.getSqlId(), processedSql);
                }
                
                location.setElementType(propertyName);
                location.setSqlType(determineSQLType(propertyName));
                
                // 计算行号（简化版本）
                int lineNumber = calculateLineNumber(xmlContent, matcher.start());
                location.setStartLine(lineNumber);
                location.setEndLine(lineNumber);
                
                sqlLocations.add(location);
                sqlIndex++;
            }
        }
    }
    
    /**
     * 提取所有bl:property标签中的SQL语句
     * 这个方法会查找所有包含SQL语句的bl:property标签，不仅限于预定义的属性名
     */
    private void extractAllBlPropertySQLs(String xmlContent, String xmlPath, List<SQLLocation> sqlLocations) {
        Matcher matcher = ALL_BL_PROPERTY_PATTERN.matcher(xmlContent);
        Set<String> processedProperties = new HashSet<>();
        
        // 记录已经处理过的属性，避免重复
        for (SQLLocation existing : sqlLocations) {
            if (existing.getElementType() != null) {
                processedProperties.add(existing.getElementType().toLowerCase());
            }
        }
        
        int sqlIndex = 1;
        while (matcher.find()) {
            String propertyName = matcher.group(1);
            String sqlContent = matcher.group(2);
            
            // 跳过已经处理过的属性
            if (processedProperties.contains(propertyName.toLowerCase())) {
                continue;
            }
            
            // 检查value是否包含SQL关键字
            if (sqlContent != null && containsSQLKeywords(sqlContent)) {
                SQLLocation location = new SQLLocation();
                location.setXmlPath(xmlPath);
                location.setSqlId(propertyName + "_" + sqlIndex);
                
                // 处理SQL内容
                String processedSql = processSQLContent(sqlContent);
                location.setSqlContent(processedSql);
                location.setElementType(propertyName);
                location.setSqlType(determineSQLTypeFromContent(processedSql));
                
                // 计算行号
                int lineNumber = calculateLineNumber(xmlContent, matcher.start());
                location.setStartLine(lineNumber);
                location.setEndLine(lineNumber);
                
                sqlLocations.add(location);
                processedProperties.add(propertyName.toLowerCase());
                sqlIndex++;
                
                log.debug("提取到bl:property SQL: name={}, content={}", propertyName, processedSql);
            }
        }
    }
    
    /**
     * 检查字符串是否包含SQL关键字
     */
    private boolean containsSQLKeywords(String content) {
        if (content == null || content.trim().isEmpty()) {
            return false;
        }
        
        String upperContent = content.toUpperCase().trim();
        return upperContent.startsWith("SELECT") ||
               upperContent.startsWith("INSERT") ||
               upperContent.startsWith("UPDATE") ||
               upperContent.startsWith("DELETE") ||
               upperContent.startsWith("CREATE") ||
               upperContent.startsWith("DROP") ||
               upperContent.startsWith("ALTER") ||
               upperContent.contains(" FROM ") ||
               upperContent.contains(" WHERE ") ||
               upperContent.contains(" SELECT ") ||
               upperContent.contains(" INSERT ") ||
               upperContent.contains(" UPDATE ") ||
               upperContent.contains(" DELETE ");
    }
    
    /**
     * 处理SQL内容，包括参数替换和特殊字符处理
     */
    private String processSQLContent(String sqlContent) {
        if (sqlContent == null || sqlContent.trim().isEmpty()) {
            return sqlContent;
        }
        
        String processed = sqlContent.trim();
        
        // 处理HTML转义字符
        processed = processed.replace("&lt;", "<")
                           .replace("&gt;", ">")
                           .replace("&amp;", "&")
                           .replace("&quot;", "\"")
                           .replace("&apos;", "'");
        
        // 处理IFP框架的参数绑定语法 #param -> ?
        Pattern paramPattern = Pattern.compile("#([a-zA-Z0-9_]+)");
        processed = paramPattern.matcher(processed).replaceAll("?");
        
        // 特殊处理INSTR函数的参数
        processed = processed.replaceAll("(?i)INSTR\\(([^,]+),\\s*([a-zA-Z0-9_]+)\\)", "INSTR($1, '$2')");
        
        return processed;
    }
    
    /**
     * 根据SQL内容确定SQL类型
     */
    private SQLType determineSQLTypeFromContent(String sqlContent) {
        if (sqlContent == null || sqlContent.trim().isEmpty()) {
            return SQLType.UNKNOWN;
        }
        
        String upperSql = sqlContent.trim().toUpperCase();
        if (upperSql.startsWith("SELECT")) {
            return SQLType.SELECT;
        } else if (upperSql.startsWith("INSERT")) {
            return SQLType.INSERT;
        } else if (upperSql.startsWith("UPDATE")) {
            return SQLType.UPDATE;
        } else if (upperSql.startsWith("DELETE")) {
            return SQLType.DELETE;
        } else {
            return SQLType.UNKNOWN;
        }
    }
    
    /**
     * 根据属性名确定SQL类型
     */
    private SQLType determineSQLType(String propertyName) {
        switch (propertyName.toLowerCase()) {
            case "querysql":
                return SQLType.SELECT;
            case "insertsql":
            case "execsql":
                return SQLType.INSERT;
            case "updatesql":
                return SQLType.UPDATE;
            case "deletesql":
                return SQLType.DELETE;
            default:
                return SQLType.UNKNOWN;
        }
    }
    
    /**
     * 计算指定位置在XML内容中的行号
     */
    private int calculateLineNumber(String xmlContent, int position) {
        if (position < 0 || position >= xmlContent.length()) {
            return 1;
        }
        
        int lineNumber = 1;
        for (int i = 0; i < position; i++) {
            if (xmlContent.charAt(i) == '\n') {
                lineNumber++;
            }
        }
        
        return lineNumber;
    }
    
    /**
     * 构建SQL提取响应
     */
    private String buildSQLExtractionResponse(XMLParseResult parseResult, String fileName) {
        try {
            ObjectNode response = objectMapper.createObjectNode();
            
            if (parseResult.isParseSuccess()) {
                response.put("success", true);
                response.put("fileName", fileName);
                response.put("businessLogicId", parseResult.getNamespace());
                response.put("frameworkType", "ifp");
                
                // 构建SQL语句数组
                ArrayNode sqlStatementsArray = objectMapper.createArrayNode();
                int validSqlCount = 0;
                int emptySqlCount = 0;
                
                if (parseResult.getSqlLocations() != null) {
                    for (SQLLocation sqlLocation : parseResult.getSqlLocations()) {
                        ObjectNode sqlNode = objectMapper.createObjectNode();
                        sqlNode.put("sqlId", sqlLocation.getSqlId());
                        sqlNode.put("elementType", sqlLocation.getElementType());
                        sqlNode.put("sqlType", sqlLocation.getSqlType().name());
                        sqlNode.put("startLine", sqlLocation.getStartLine());
                        sqlNode.put("endLine", sqlLocation.getEndLine());
                        
                        // 检查是否为空SQL
                        boolean isEmpty = sqlLocation.getSqlContent() == null || sqlLocation.getSqlContent().trim().isEmpty();
                        sqlNode.put("isEmpty", isEmpty);
                        
                        if (isEmpty) {
                            emptySqlCount++;
                        } else {
                            validSqlCount++;
                        }
                        
                        // 从xmlPath中提取原始SQL（如果存在）
                        String originalSql = "";
                        if (sqlLocation.getXmlPath() != null && sqlLocation.getXmlPath().startsWith("ORIGINAL:")) {
                            originalSql = sqlLocation.getXmlPath().substring(9); // 移除"ORIGINAL:"前缀
                        }
                        
                        sqlNode.put("originalSql", originalSql);
                        sqlNode.put("fixedSql", sqlLocation.getSqlContent());
                        sqlNode.put("isComplete", isSQLComplete(sqlLocation.getSqlContent()));
                        
                        sqlStatementsArray.add(sqlNode);
                    }
                }
                
                response.put("validSqlCount", validSqlCount);
                response.put("emptySqlCount", emptySqlCount);
                
                response.set("sqlStatements", sqlStatementsArray);
                response.put("totalCount", sqlStatementsArray.size());
                
            } else {
                response.put("success", false);
                response.put("errorMessage", parseResult.getErrorMessage());
            }
            
            return objectMapper.writeValueAsString(response);
            
        } catch (Exception e) {
            log.error("构建SQL提取响应失败: {}", fileName, e);
            return formatErrorResponse("构建响应失败: " + e.getMessage());
        }
    }
    
    /**
     * 创建错误结果
     */
    private XMLParseResult createErrorResult(String xmlPath, String errorMessage) {
        XMLParseResult result = XMLParseResult.forIfp(xmlPath);
        result.setXmlPath(xmlPath);
        result.setParseSuccess(false);
        result.setErrorMessage(errorMessage);
        return result;
    }
    
    /**
     * 格式化错误响应
     */
    private String formatErrorResponse(String errorMessage) {
        try {
            ObjectNode response = objectMapper.createObjectNode();
            response.put("success", false);
            response.put("errorMessage", errorMessage);
            return objectMapper.writeValueAsString(response);
        } catch (Exception e) {
            log.error("格式化错误响应失败", e);
            return "{\"success\": false, \"errorMessage\": \"格式化错误响应失败\"}";
        }
    }
    
    /**
     * 检查是否为非IFP XML文件
     */
    private boolean isNonIfpXmlFile(String content) {
        if (content == null || content.trim().isEmpty()) {
            return false;
        }
        
        String trimmed = content.trim().toLowerCase();
        
        // 检查是否为Spring配置文件
        if (trimmed.contains("<beans") && trimmed.contains("xmlns") && 
            (trimmed.contains("spring") || trimmed.contains("bl:businesslogic"))) {
            return true;
        }
        
        // 检查是否包含IFP相关的SQL属性
        boolean hasIfpElements = false;
        for (String sqlProperty : SQL_PROPERTY_NAMES) {
            if (trimmed.contains(sqlProperty)) {
                hasIfpElements = true;
                break;
            }
        }
        
        // 如果没有IFP SQL属性，且包含其他框架标签，则认为是非IFP文件
        if (!hasIfpElements && (trimmed.contains("<bl:") || trimmed.contains("<action"))) {
            return true;
        }
        
        return false;
    }
}